Date Grouping Tolerances Appendix

This section explains Date Grouping Tolerances and how to set them up in OneStream. To help explain Date Grouping Tolerances, we'll use two data sets to show how matches work when you have:

These are the data sets we'll use to explain the different scenarios. They both have a list of five transactions containing invoices with dates and amounts.

No Date Tolerances

If we do not apply date tolerances to the data sets then the transactions are grouped by the same invoice and date to get the aggregated amounts. All AAAA invoices on 2-Jan are summed together, as are all AAAA invoices on 3-Jan and all BBBB invoices on 3-Jan.

As noted in the image below, there are no matches across the aggregated amounts. The sum of the AAAA invoices on 2-Jan for DS1 is $300 while DS2 is $425. The sum of the AAAA invoices on 3-Jan for DS1 is $75 while DS2 is $375. The sum of the BBBB invoices on 3-Jan for DS1 is $200 while DS2 is $300.

Post-aggregate Date Tolerances

When we add a post-aggregate date tolerance, of 1 day before and 1 day after the first step is the same as in the previous example: we sum all the transactions by invoice and date. Then we add an additional step of taking the aggregated amounts and applying the date tolerances after the fact.

Let's look at just one of the aggregated amounts in DS1 (the aggregated AAAA invoices on 2-Jan for $300, which we saw in the previous example is actually made up of two transactions). In applying the date tolerance post grouping we will then look in DS2 for invoice AAAA on 2-Jan for an aggregated amount of $300 and also look to see if there is this amount for invoice AAAA on a day before (on 1-Jan) or a day after (on 3-Jan).

But if we look at the aggregated amounts for the AAAA invoices we had in our DS2 we see there is no match (they total $425 or $375, but not $300). Again, we just reviewed one of the aggregated amounts, but if we did this for the other aggregated transactions (AAAA invoices on 3-Jan and BBBB invoices on 3-Jan), we would still find no matches.

Pre-aggregate Date Tolerances

For pre-aggregate date tolerances, we need to specify which data set we want the date tolerance applied to. In this example we won't apply a tolerance to DS2 since we have already seen that the transactions easily group by the same invoice and date. So the transactions in DS2 will be aggregated the same as in the previous examples. The AAAA invoices on 3-Jan equal $375 and the BBBB invoices on 3-Jan equal $300.

For DS1, we'll apply the pre-aggregate date tolerance of 1 day before and 1 day after. Starting with the first line item for invoice AAAA on 2-Jan for $100, all invoice AAAAs within one day before (on 1-Jan) and one day after (on 3-Jan) are aggregated. There's nothing on 1-Jan but now the 3-Jan invoice for $75 is included so the total is $375.

The same happens for the BBBB invoices. Starting with the BBBB invoice on 3-Jan for $200, the amounts on 2-Jan and 4-Jan are included. There's nothing on 2-Jan, but now the 4-Jan invoice is included so the total is $300. As a result, there are two matches, where in the first two scenarios there were no matches.

Set Up Date Tolerances

Following are instructions for setting up date tolerances to match the scenarios that were described above.

Post-aggregate Date Tolerances

To set up the post-aggregate date tolerances as in the scenario above, you set up the date tolerances in the Definition tab and then group by Invoice attribute field in the Grouping tab.

Pre-aggregate Date Tolerances

To set up the pre-aggregate date tolerances as in the scenario above, you set up the date tolerances in the Grouping tab and group by the Invoice attribute field.